package com.infodms.dms.dao.sales.planmanage;

import java.sql.ResultSet;
import java.util.LinkedList;
import java.util.List;
import java.util.Map;


import org.apache.log4j.Logger;

import com.infodms.dms.actions.sales.planmanage.PlanUtil.PlanUtil;
import com.infodms.dms.common.Constant;
import com.infodms.dms.dao.common.BaseDao;
import com.infodms.dms.po.TmOrgPO;
import com.infodms.dms.po.TmVhclMaterialGroupPO;
import com.infodms.dms.po.TmpVsYearlyPlanPO;
import com.infodms.dms.po.TtVsYearlyPlanDetailPO;
import com.infodms.dms.po.TtVsYearlyPlanPO;
import com.infoservice.po3.bean.PO;
import com.infoservice.po3.bean.PageResult;


public class YearPlanDao extends BaseDao {

	
	public static Logger logger = Logger.getLogger(YearPlanDao.class);
	private static final YearPlanDao dao = new YearPlanDao ();
	public static final YearPlanDao getInstance() {
		return dao;
	}
	/*
	 * 清除业务表
	 */
	public int clrTable(Map<String, Object> map) {
		String year=map.get("year").toString();
		String planType=map.get("planType").toString();
		String orgType=map.get("orgType").toString();
		String areaId=map.get("areaId").toString();
		String companyId=map.get("companyId").toString();
		StringBuffer sql = new StringBuffer();

		sql.append("delete TT_VS_YEARLY_PLAN p\n");
		sql.append(" where p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		sql.append("   and p.ORG_TYPE = "+orgType+"\n");  
		sql.append("   and p.AREA_ID = "+areaId+"\n");  
		sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("   and p.PLAN_TYPE = "+planType+"\n");

		return dao.update(sql.toString(), null);
	}

	/*
	 * 清除业务表
	 */
	public int clrDetailTable(Map<String, Object> map) {
		String year=map.get("year").toString();
		String planType=map.get("planType").toString();
		String orgType=map.get("orgType").toString();
		String areaId=map.get("areaId").toString();
		String companyId=map.get("companyId").toString();
		StringBuffer sql = new StringBuffer();

		sql.append("delete TT_VS_YEARLY_PLAN_DETAIL cascade\n");
		sql.append(" where not exists (select 1\n");  
		sql.append("   from TT_VS_YEARLY_PLAN p\n");
		sql.append(" where \n");  
		/*sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		sql.append("   and p.ORG_TYPE = "+orgType+"\n");  
		sql.append("   and p.AREA_ID = "+areaId+"\n");  
		sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("   and p.PLAN_TYPE = "+planType+"\n");*/
		sql.append("   p.PLAN_ID = PLAN_ID  ) \n");

		return dao.update(sql.toString(), null);
	}
	/*
	 * 清除业务表
	 */
	public int sbuClrTable(Map<String, Object> map) {
		String year=map.get("year").toString();
		String planType=map.get("planType").toString();
		String orgType=map.get("orgType").toString();
		String areaId=map.get("areaId").toString();
		String orgId=map.get("orgId").toString();
		String companyId=map.get("companyId").toString();
		StringBuffer sql = new StringBuffer();

		sql.append("delete TT_VS_YEARLY_PLAN p\n");
		sql.append(" where p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		sql.append("   and p.ORG_TYPE = "+orgType+"\n");  
		sql.append("   and p.AREA_ID = "+areaId+"\n");  
		sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("   and p.PLAN_TYPE = "+planType+"\n");
		sql.append("   and p.DEALER_ID in\n");
		sql.append("       (select d.DEALER_ID\n");  
		sql.append("          from tm_dealer d\n");  
		sql.append("         start with d.DEALER_ID in\n");  
		sql.append("                    (select td.DEALER_ID\n");  
		sql.append("                       from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
		sql.append("                      where td.dealer_id = r.dealer_id\n");  
		sql.append("                        and td.OEM_COMPANY_ID = p.COMPANY_ID\n");  
		sql.append("                        and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                        and r.ORG_ID = "+orgId+")\n");
		sql.append("connect by prior d.DEALER_ID = d.PARENT_DEALER_D\n");
		sql.append("        )");


		return dao.update(sql.toString(), null);
	}

	/*
	 * 清除业务表
	 */
	public int sbuClrDetailTable(Map<String, Object> map) {
		String year=map.get("year").toString();
		String planType=map.get("planType").toString();
		String orgType=map.get("orgType").toString();
		String areaId=map.get("areaId").toString();
		String companyId=map.get("companyId").toString();
		StringBuffer sql = new StringBuffer();

		sql.append("delete TT_VS_YEARLY_PLAN_DETAIL cascade\n");
		sql.append(" where not exists (select 1\n");  
		sql.append("   from TT_VS_YEARLY_PLAN p\n");
		sql.append(" where \n");  
		//sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		//sql.append("   and p.ORG_TYPE = "+orgType+"\n");  
		//sql.append("   and p.AREA_ID = "+areaId+"\n");  
		//sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		//sql.append("   and p.PLAN_TYPE = "+planType+"\n");
		sql.append("   p.PLAN_ID = PLAN_ID  ) \n");

		return dao.update(sql.toString(), null);
	}
	
	public int updateForConfrim(Map<String, Object> map,TtVsYearlyPlanPO po){
		String year=map.get("year").toString();
		String planType=map.get("planType").toString();
		String areaId=map.get("areaId").toString();
		String orgId=map.get("orgId").toString();
		String companyId=map.get("companyId").toString();
		
		StringBuffer sql = new StringBuffer();

		sql.append("update TT_VS_YEARLY_PLAN p\n");
		sql.append("   set p.PLAN_VER = "+po.getPlanVer()+", p.PLAN_DESC = '"+po.getPlanDesc()+"', p.STATUS = "+Constant.PLAN_MANAGE_CONFIRM+",p.UPDATE_BY="+po.getUpdateBy()+",p.UPDATE_DATE=sysdate\n");  
		sql.append(" where p.PLAN_TYPE = "+planType+"\n");  
		sql.append("   and p.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		sql.append("   and p.AREA_ID = "+areaId+"");
		sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("   and p.DEALER_ID in\n");
		sql.append("      (select d.DEALER_ID\n");  
		sql.append("         from tm_dealer d\n");  
		sql.append("        start with d.DEALER_ID in\n");  
		sql.append("                   (select td.DEALER_ID\n");  
		sql.append("                      from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
		sql.append("                     where td.dealer_id = r.dealer_id\n");  
		sql.append("                       and td.OEM_COMPANY_ID = "+companyId+"\n");  
		sql.append("                       and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                       and r.ORG_ID = "+orgId+")\n");
		sql.append("   connect by prior d.DEALER_ID = d.PARENT_DEALER_D)\n");
		
		return dao.update(sql.toString(), null);

	}
	/*
	 * 插入主表数据
	 */
	public int insertPlan(Map<String, Object> map) {
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String areaId=map.get("areaId").toString();
		String companyId=map.get("companyId").toString();

		StringBuffer sql = new StringBuffer();

		sql.append("merge into TT_VS_YEARLY_PLAN tp\n");
		sql.append("using (select F_GETID() PLAN_ID,\n");  
		sql.append("              "+companyId+" COMPANY_ID,\n");  
		sql.append("              "+areaId+" AREA_ID,\n");  
		sql.append("              aa.PLAN_YEAR,\n");  
		sql.append("              aa.PLAN_MONTH,\n");
		sql.append("              "+Constant.ORG_TYPE_OEM+" ORG_TYPE,\n");
		sql.append("              aa.ORG_ID,\n");
		sql.append("             "+Constant.PLAN_TYPE_BUY+" PLAN_TYPE,\n");
		sql.append("              "+Constant.PLAN_MANAGE_UNCONFIRM+" STATUS,\n");  
		sql.append("              0 VER,\n");  
		sql.append("              "+userId+" CREATE_BY,\n");  
		sql.append("              sysdate CREATE_DATE\n");  
		sql.append("         from (select distinct p.PLAN_YEAR,org.ORG_ID\n");  
		sql.append("                 from TMP_VS_YEARLY_PLAN p,TM_ORG org\n");  
		sql.append("                where p.ORG_CODE=org.ORG_CODE and p.PLAN_YEAR = "+year+"\n");  
		sql.append("                  ) aa) a\n");  
		sql.append("on (tp.COMPANY_ID = a.COMPANY_ID  and tp.PLAN_YEAR = a.PLAN_YEAR and tp.ORG_ID = a.ORG_ID and tp.ORG_TYPE = a.ORG_TYPE  and tp.STATUS = a.STATUS)\n");  
		sql.append("when not matched then\n");  
		sql.append("  insert\n");  
		sql.append("    (PLAN_ID,\n");  
		sql.append("     COMPANY_ID,\n");  
		sql.append("     PLAN_YEAR,\n");  
		sql.append("     ORG_TYPE,\n");
		sql.append("     ORG_ID,\n");
		sql.append("     STATUS,\n");  
		sql.append("     VER,\n");  
		sql.append("     PLAN_TYPE,\n");
		sql.append("     CREATE_BY,\n");  
		sql.append("     CREATE_DATE)\n");  
		sql.append("  values\n");  
		sql.append("    (a.PLAN_ID,\n");  
		sql.append("     a. COMPANY_ID,\n");  
		sql.append("     A.PLAN_YEAR,\n");  
		sql.append("     a.ORG_TYPE,\n"); 
		sql.append("     a.ORG_ID,\n");  
		sql.append("     a.STATUS,\n");  
		sql.append("     a.VER,\n");  
		sql.append("     a.PLAN_TYPE,\n");
		sql.append("     a.CREATE_BY,\n");  
		sql.append("     a. CREATE_DATE)");

		return update(sql.toString(), null);
	}
	
	/*
	 * 插入明细表数据
	 */
	public int insertPlanDetail(Map<String, Object> map) {
		String year=map.get("year").toString();
		String month=map.get("month").toString();
		String userId=map.get("userId").toString();
		String areaId=map.get("areaId").toString();
		String companyId=map.get("companyId").toString();

		StringBuffer sql = new StringBuffer();

		sql.append("MERGE into TT_VS_YEARLY_PLAN_DETAIL a\n");
		sql.append("using (select tpp.PLAN_ID, tvmg.GROUP_ID, tmpp.SUM_AMT\n");  

		sql.append("        from TT_VS_YEARLY_PLAN  tpp,\n");
		sql.append("             TMP_VS_YEARLY_PLAN tmpp,\n");  
		sql.append("             TM_ORG                 org,\n");  
		sql.append("             TM_VHCL_MATERIAL_GROUP tvmg\n");  
		sql.append("       where tvmg.GROUP_CODE = tmpp.GROUP_CODE\n");  
		sql.append("         and tmpp.GROUP_CODE = tvmg.GROUP_CODE\n");  
		sql.append("         and tpp.ORG_ID=org.ORG_ID\n");  
		sql.append("         and tmpp.ORG_CODE=org.ORG_CODE\n");  
		sql.append("         and tpp.PLAN_YEAR = tmpp.PLAN_YEAR\n");  
		sql.append("         and tpp.PLAN_MONTH = tmpp.PLAN_MONTH\n");  
		sql.append("         and tpp.COMPANY_ID = tvmg.COMPANY_ID");

		sql.append("          and tpp.COMPANY_ID ="+companyId+"\n");  
		sql.append("          and tpp.AREA_ID ="+areaId+"\n");  
		sql.append("          and tpp.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("          and tmpp.USER_ID ="+userId+") c\n");  
		sql.append("on (a.PLAN_ID = c.PLAN_ID and a.MATERIAL_GROUPID = c.GROUP_ID)\n");  
		sql.append("when MATCHED then\n");  
		sql.append("  update\n");  
		sql.append("     set a.SALE_AMOUNT = c.SUM_AMT,\n");  
		sql.append("         a.UPDATE_BY   = "+userId+",\n");  
		sql.append("         a.UPDATE_DATE = sysdate\n");  
		sql.append("when not matched then\n");  
		sql.append("  INSERT\n");  
		sql.append("    (a.DETAIL_ID,\n");  
		sql.append("     a.PLAN_ID,\n");  
		sql.append("     a.MATERIAL_GROUPID,\n");  
		sql.append("     a.SALE_AMOUNT,\n");  
		sql.append("     a.CREATE_BY,\n");  
		sql.append("     a.CREATE_DATE)\n");  
		sql.append("  values\n");  
		sql.append("    (F_GETID(), c.PLAN_ID, c.GROUP_ID, c.SUM_AMT, "+userId+", sysdate)");

		return update(sql.toString(), null);
	}
	/*
	 * 查询未确认年度目标日期-业务范围-计划类型
	 */
	public  List<Map<String, Object>> unConfirmSelectSearch(Map<String, Object> map){
		String companyId=map.get("companyId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select DISTINCT p.PLAN_YEAR || ',' || p.AREA_ID || ',' || p.PLAN_TYPE keyStr,\n");
		sql.append("                p.PLAN_YEAR || '-' || a.AREA_NAME || '-' || c.CODE_DESC valStr\n");  
		sql.append("  from TT_VS_YEARLY_PLAN p, TM_BUSINESS_AREA a, TC_CODE c\n");  
		sql.append(" where p.AREA_ID = a.AREA_ID\n");  
		sql.append("   and p.PLAN_TYPE = c.CODE_ID\n");  
		sql.append("   and p.COMPANY_ID = "+companyId+"\n");  
		sql.append("   and p.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("   and p.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标导入(车厂导入区域)
	 * 校验组织是否存在
	 */
	public  List<Map<String, Object>> oemYearPlanCheckOrg(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String companyId=map.get("companyId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is null\n");
		sql.append("   and not exists (select 1\n");  
		sql.append("          from tm_org org\n");  
		sql.append("         where org.ORG_CODE = p.ORG_CODE\n");  
		sql.append("           and org.COMPANY_ID = "+companyId+"\n");//多公司过滤
		sql.append("           and org.STATUS = "+Constant.STATUS_ENABLE+"\n");//有效
		sql.append("           and org.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");//车厂组织
		sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+")\n");//大区
		sql.append("   order by to_number(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标校验组织是否与业务范围一致
	 */
	public  List<Map<String, Object>> oemYearPlanCheckOrgArea(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is null\n");
		sql.append("   and not exists (select 1\n");  
		sql.append("        from tm_org org, TM_ORG_BUSINESS_AREA area\n");
		sql.append("        where org.ORG_CODE = p.ORG_CODE\n");  
		sql.append("           and org.ORG_ID = area.ORG_ID\n"); 
		sql.append("           and org.STATUS = "+Constant.STATUS_ENABLE+"\n");//有效
		sql.append("           and area.AREA_ID = "+areaId+"\n");
		sql.append("           and org.COMPANY_ID = "+companyId+"\n");//多公司过滤
		sql.append("           and org.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");//车厂组织
		sql.append("           and org.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+")\n");//大区
		sql.append("   order by TO_NUMBER(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标导入(车厂导入区域)
	 * 校验车系是否存在
	 */
	public  List<Map<String, Object>> oemYearPlanCheckGroup(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String companyId=map.get("companyId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is null\n");
		sql.append("   and not exists (select 1\n");
		sql.append("       from TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("      where g.GROUP_CODE = p.GROUP_CODE\n"); 
		sql.append("        and g.STATUS = "+Constant.STATUS_ENABLE+"\n");//有效
		sql.append("        and g.COMPANY_ID = "+companyId+")\n");  
		sql.append("   order by to_number(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标校验车系是否与业务范围一致
	 */
	public  List<Map<String, Object>> oemYearPlanCheckGroupArea(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String groupArea=map.get("groupArea").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID = "+userId+"\n");  
		sql.append("   and p.DEALER_CODE is null\n");  
		sql.append("   and p.GROUP_CODE not in("+PlanUtil.createSqlStr(groupArea)+")\n");  
		sql.append(" order by TO_NUMBER(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标导入(区域导入经销商)
	 * 校验经销商是否存在
	 */
	public  List<Map<String, Object>> sbuYearPlanCheckOrg(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String orgId=map.get("orgId").toString();
		String companyId=map.get("companyId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is not null\n");
		/*sql.append("   and not exists (select 1 \n");  
		sql.append("          from TM_DEALER dealer, TM_DEALER_ORG_RELATION r \n");  
		sql.append("         where dealer.DEALER_CODE = p.DEALER_CODE \n"); 
		sql.append("           and dealer.DEALER_ID = r.DEALER_ID \n");
		sql.append("           and r.ORG_ID = "+orgId+" \n");
		sql.append("           and dealer.OEM_COMPANY_ID = "+companyId+" \n");//多公司过滤
		sql.append("           and dealer.STATUS = "+Constant.STATUS_ENABLE+") \n");//有效
*/		
		sql.append("and p.DEALER_CODE not in\n");
		sql.append("      (select d.DEALER_CODE\n");  
		sql.append("         from tm_dealer d\n");  
		sql.append("        start with d.DEALER_ID in\n");  
		sql.append("                   (select td.DEALER_ID\n");  
		sql.append("                      from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
		sql.append("                     where td.dealer_id = r.dealer_id\n");  
		sql.append("                       and td.OEM_COMPANY_ID = "+companyId+"\n");  
		sql.append("                       and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                       and r.ORG_ID = "+orgId+")\n");
		sql.append("connect by prior d.DEALER_ID = d.PARENT_DEALER_D\n");
		sql.append("        )");
		sql.append("   order by to_number(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标导入(区域导入经销商)
	 * 年度目标校验经销商是否与业务范围一致
	 */
	public  List<Map<String, Object>> sbuYearPlanCheckDlrArea(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String companyId=map.get("companyId").toString();
		String areaId=map.get("areaId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is not null\n");
		sql.append("   and p.ORG_CODE is null\n");
		sql.append("   and not exists (select 1\n");  
		sql.append("        from TM_DEALER td, TM_DEALER_BUSINESS_AREA area\n");
		sql.append("        where td.DEALER_CODE = p.DEALER_CODE\n");  
		sql.append("           and td.DEALER_ID = area.DEALER_ID\n"); 
		sql.append("           and td.STATUS = "+Constant.STATUS_ENABLE+"\n");//有效
		sql.append("           and area.AREA_ID = "+areaId+"\n");
		sql.append("           and td.OEM_COMPANY_ID = "+companyId+"\n");//多公司过滤
		sql.append("           )\n");//车厂组织
		//sql.append("           and td.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+")\n");//大区
		sql.append("   order by TO_NUMBER(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 年度目标导入(区域导入经销商)
	 * 校验车系是否存在
	 */
	public  List<Map<String, Object>> sbuYearPlanCheckGroup(Map<String, Object> map){
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		String companyId=map.get("companyId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select p.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p\n");  
		sql.append(" where 1 = 1\n");  
		sql.append("   and p.PLAN_YEAR = "+year+"\n");  
		sql.append("   and p.USER_ID ="+userId+"\n");  
		sql.append("   and p.DEALER_CODE is null\n");
		sql.append("   and not exists (select 1\n");
		sql.append("       from TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("      where g.GROUP_CODE = p.GROUP_CODE\n"); 
		sql.append("        and g.STATUS = "+Constant.STATUS_ENABLE+"\n");//有效
		sql.append("        and g.COMPANY_ID = "+companyId+")\n");  
		sql.append("   order by to_number(p.ROW_NUMBER) asc");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 总部导入区域
	 * 查询临时表中是否有相同的ORG导入相同的车系
	 * 返回所有重复数据集合
	 */
	public List<Map<String, Object>> oemTalbeCheckDump(String year,String userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");


		sql.append("select p1.ROW_NUMBER ROW_NUMBER1, p2.ROW_NUMBER ROW_NUMBER2\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p1, TMP_VS_YEARLY_PLAN p2\n");  
		sql.append(" where p1.org_CODE = p2.org_CODE\n");  
		sql.append("   and p1.GROUP_CODE = p2.GROUP_CODE\n");  
		sql.append("   and p1.ROW_NUMBER <> p2.ROW_NUMBER\n");  
		sql.append("   and p1.PLAN_YEAR = p2.PLAN_YEAR\n");  
		sql.append("   and p1.USER_ID = p2.USER_ID\n");  
		sql.append("   and p1.ORG_CODE is not null\n");  
		sql.append("   and p1.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and p1.USER_ID=?");
		params.add(userId);
		

		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	/*
	 * 区域导入经销商
	 * 查询临时表中是否有相同的ORG导入相同的车系
	 * 返回所有重复数据集合
	 */
	public List<Map<String, Object>> sbuTalbeCheckDump(String year,String userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");


		sql.append("select p1.ROW_NUMBER ROW_NUMBER1, p2.ROW_NUMBER ROW_NUMBER2\n");
		sql.append("  from TMP_VS_YEARLY_PLAN p1, TMP_VS_YEARLY_PLAN p2\n");  
		sql.append(" where p1.DEALER_CODE = p2.DEALER_CODE\n");  
		sql.append("   and p1.GROUP_CODE = p2.GROUP_CODE\n");  
		sql.append("   and p1.ROW_NUMBER <> p2.ROW_NUMBER\n");  
		sql.append("   and p1.PLAN_YEAR = p2.PLAN_YEAR\n");  
		sql.append("   and p1.USER_ID = p2.USER_ID\n");  
		sql.append("   and p1.ORG_CODE is  null\n");  
		sql.append("   and p1.DEALER_CODE is not null\n"); 
		sql.append("   and p1.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and p1.USER_ID=?");
		params.add(userId);
		

		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	
	/*
	 * 总部导入区域
	 * 查询临时表中是ORG与GROUP是否在同一业务范围
	 */
	public List<Map<String, Object>> deptTalbeCheckGroupArea(Map<String, Object> map){
		
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");


		sql.append("select tp.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN tp\n");  
		sql.append(" where tp.PLAN_YEAR = "+year+"\n");  
		sql.append("   and tp.USER_ID = "+userId+"\n");  
		sql.append("   and tp.ORG_CODE is not null\n");  
		sql.append("   and tp.DEALER_CODE is  null\n");  
		sql.append("   and not\n");  
		sql.append("        exists((SELECT 1\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("                 WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("                   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                   and tvmg.GROUP_CODE = tp.GROUP_CODE\n");  
		sql.append("                 START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                               FROM TM_AREA_GROUP TAG\n");  
		sql.append("                              WHERE TAG.AREA_ID in\n");  
		sql.append("                                    (select area_id\n");  
		sql.append("                                       from TM_ORG_BUSINESS_AREA area,\n");  
		sql.append("                                            TM_ORG               org\n");  
		sql.append("                                      where org.ORG_ID = area.ORG_ID\n");  
		sql.append("                                        and org.ORG_CODE = tp.ORG_CODE))\n");  
		sql.append("                CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID)\n");  
		sql.append("                union\n");  
		sql.append("               (SELECT 1\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("                 WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("                   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                   and tvmg.GROUP_CODE = tp.GROUP_CODE\n");  
		sql.append("                 START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                               FROM TM_AREA_GROUP TAG\n");  
		sql.append("                              WHERE TAG.AREA_ID in\n");  
		sql.append("                                    (select area_id\n");  
		sql.append("                                       from TM_ORG_BUSINESS_AREA area,\n");  
		sql.append("                                            TM_ORG               org\n");  
		sql.append("                                      where org.ORG_ID = area.ORG_ID\n");  
		sql.append("                                        and org.ORG_CODE = tp.ORG_CODE))\n");  
		sql.append("                CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID)\n");  
		sql.append("               )\n");  
		sql.append("order by to_number(tp.ROW_NUMBER)");



		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	
	/*
	 * 区域导入经销商
	 * 查询临时表中是否有相同的ORG导入相同的车系
	 * 返回所有重复数据集合
	 */
	public List<Map<String, Object>> sbuTalbeCheckGroupArea(Map<String, Object> map){
		
		String year=map.get("year").toString();
		String userId=map.get("userId").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select tp.ROW_NUMBER\n");
		sql.append("  from TMP_VS_YEARLY_PLAN tp\n");  
		sql.append(" where tp.PLAN_YEAR = "+year+"\n");  
		sql.append("   and tp.USER_ID = "+userId+"\n");  
		sql.append("   and tp.ORG_CODE is null\n");  
		sql.append("   and tp.DEALER_CODE is not null\n");  
		sql.append("   and not\n");  
		sql.append("        exists((SELECT 1\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("                 WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("                   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                   and tvmg.GROUP_CODE = tp.GROUP_CODE\n");  
		sql.append("                 START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                               FROM TM_AREA_GROUP TAG\n");  
		sql.append("                              WHERE TAG.AREA_ID in\n");  
		sql.append("                                    (select area_id\n");  
		sql.append("                                       from TM_DEALER_BUSINESS_AREA area,\n");  
		sql.append("                                            TM_DEALER               td\n");  
		sql.append("                                      where td.DEALER_ID = area.DEALER_ID\n");  
		sql.append("                                        and td.DEALER_CODE = tp.DEALER_CODE))\n");  
		sql.append("                CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID)\n");  
		sql.append("                union\n");  
		sql.append("               (SELECT 1\n");  
		sql.append("                  from TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("                 WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("                   AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                   and tvmg.GROUP_CODE = tp.GROUP_CODE\n");  
		sql.append("                 START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                            (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                               FROM TM_AREA_GROUP TAG\n");  
		sql.append("                              WHERE TAG.AREA_ID in\n");  
		sql.append("                                    (select area_id\n");  
		sql.append("                                       from TM_DEALER_BUSINESS_AREA area,\n");  
		sql.append("                                            TM_DEALER               td\n");  
		sql.append("                                      where td.DEALER_ID = area.DEALER_ID\n");  
		sql.append("                                        and td.DEALER_CODE = tp.DEALER_CODE))\n");  
		sql.append("                CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID)\n");  
		sql.append("               )\n");  
		sql.append("order by to_number(tp.ROW_NUMBER)");


		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	/**
	 * 将临时表中对应年度的临时数据删除
	 * @param year
	 */
	public void deleteTmpYearPlan(String year,Long userId){
		List<Object> params=new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		sql.append("DELETE TMP_VS_YEARLY_PLAN TMP WHERE TMP.PLAN_YEAR = ?\n");
		params.add(year);
		sql.append(" AND USER_ID=?");
		params.add(userId);
		logger.info("清空临时表"+sql.toString());
        dao.delete(sql.toString(), params);
	}
	/**
	 * 向临时表插入数据
	 * 
	 */
	public void insertTmpYearPlan(TmpVsYearlyPlanPO po){
		dao.insert(po);
		logger.info("insert tmp_yearly_plan success!");
	}
	
	/*
	 * 查询经销商和登陆用户的业务范围是否一致，需要时打开注释
	 */
	public boolean selectSingleDealer(Long orgId,String dealerCode){
		List<Object> params=new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();


		sql.append("select td.DEALER_ID, oa.ORG_ID, td.DEALER_CODE, da.AREA_ID, oa.AREA_ID\n");
		sql.append("  from TM_DEALER_BUSINESS_AREA da, TM_DEALER td, TM_ORG_BUSINESS_AREA oa\n");  
		sql.append(" where da.DEALER_ID = td.DEALER_ID\n");  
		sql.append("   and da.AREA_ID = oa.AREA_ID\n");  
		sql.append("   and oa.ORG_ID = ?\n");  
		params.add(orgId);
		sql.append("   and td.dealer_code = ?");
		params.add(dealerCode);
		sql.append("and td.DEALER_ID in\n");
		sql.append("       (select r.DEALER_ID from TM_DEALER_ORG_RELATION r where r.ORG_ID = ?)");
		params.add(orgId);

		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		boolean isExists=true;
		if(null==list||list.size()==0){
			isExists=false;
		}
		return isExists;
	}
	/*
	 * 单表查询经销商信息
	 */
	public boolean selectDealerOrg(Long orgId,String dealerCode){
		List<Object> params=new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select *\n");
		sql.append("  from TM_dealer td, TM_DEALER_ORG_RELATION r, tm_org org\n");  
		sql.append(" where td.DEALER_ID = r.DEALER_ID\n");  
		sql.append("   and org.ORG_ID = r.ORG_ID\n");  
		sql.append("   and org.ORG_ID = ?\n");  
		params.add(orgId);
		sql.append("   and td.DEALER_CODE = ?\n");  
		params.add(dealerCode);
		
		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		boolean isExists=true;
		if(null==list||list.size()==0){
			isExists=false;
		}
		return isExists;
	}
	/*
	 * 单表查询组织商信息
	 * 返回true表示存在，否则不存在
	 */
	public boolean selectSingleOrg(TmOrgPO po){
		List<TmOrgPO> list=dao.select(po);
		boolean isExists=true;
		if(null==list||list.size()==0){
			isExists=false;
		}
		return isExists;
	}
	/*
	 * orgId和登陆用户的业务范围是否一致，需要时打开注释
	 */
	public boolean selectOrgArea(Long orgId,String orgCode){
		List<Object> params=new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select *\n");
		sql.append("  from TM_ORG org, TM_ORG_BUSINESS_AREA oa1, TM_ORG_BUSINESS_AREA oa2\n");  
		sql.append(" where org.ORG_ID = oa1.ORG_ID\n");  
		sql.append("   and oa1.AREA_ID = oa2.ORG_ID\n");  
		sql.append("   and oa2.ORG_ID = ?\n");  
		params.add(orgId);
		sql.append("   and org.ORG_CODE = ?");
        params.add(orgCode);

		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		boolean isExists=true;
		if(null==list||list.size()==0){
			isExists=false;
		}
		return isExists;
	}
	/*
	 * 单表查询物料组信息
	 */
	public List<TmVhclMaterialGroupPO> selectSingleMaterialGroup(TmVhclMaterialGroupPO po){
		return dao.select(po);
	}
	
	/*
	 * 删除业务表中，用户导入的未确认年度目标
	 */
	public void clearUserYearlyPlan(TtVsYearlyPlanPO po){
		TtVsYearlyPlanPO cPo=new TtVsYearlyPlanPO();
		cPo.setPlanId(po.getPlanId());
		dao.delete(cPo);
	}
	/*
	 * 单表查询TMP_YEARLY_PLAN
	 */
	public List<TmpVsYearlyPlanPO> selectTmpYearlyPlan(TmpVsYearlyPlanPO po){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("SELECT *\n");
		sql.append("  FROM Tmp_Vs_Yearly_Plan\n");  
		sql.append(" WHERE 1 = 1\n");  
		sql.append("   AND User_Id = ?\n");  
		params.add(po.getUserId());
		/*sql.append("   AND Plan_Year = ?\n");
		params.add(po.getPlanYear());*/
		sql.append(" order by row_number");

		return dao.select(TmpVsYearlyPlanPO.class, sql.toString(), params);
	}
	
	public List<Map<String, Object>> selectTmpYearPlan(String year,Long userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("SELECT TD.DEALER_SHORTNAME,\n");
		sql.append("       TVMG.GROUP_NAME,\n");  
		sql.append("       TMP.ROW_NUMBER,\n");  
		sql.append("       TMP.SUM_AMT,\n");  
		sql.append("       TMP.JAN_AMT,\n");  
		sql.append("       TMP.FEB_AMT,\n");  
		sql.append("       TMP.MAR_AMT,\n");  
		sql.append("       TMP.APR_AMT,\n");  
		sql.append("       TMP.MAY_AMOUNT,\n");  
		sql.append("       TMP.JUN_AMT,\n");  
		sql.append("       TMP.JUL_AMT,\n");  
		sql.append("       TMP.AUG_AMT,\n");  
		sql.append("       TMP.SEP_AMT,\n");  
		sql.append("       TMP.OCT_AMT,\n");  
		sql.append("       TMP.NOV_AMT,\n");  
		sql.append("       TMP.DEC_AMT\n");  
		sql.append("  FROM TMP_VS_YEARLY_PLAN TMP, TM_DEALER TD, TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TMP.DEALER_CODE = TD.DEALER_CODE\n");  
		sql.append("   AND TVMG.GROUP_CODE = TMP.GROUP_CODE\n");
		sql.append("   AND tmp.PLAN_YEAR=?\n");
		params.add(year);
		sql.append("   AND tmp.USER_ID=?");
		params.add(userId);
		
		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, "com.infodms.dms.dao.sales.planmanage.selectTmpYearPlan");
	}
	/*
	 * OEM导入时，查询临时表数据
	 */
	public List<Map<String, Object>> oemSelectTmpYearPlan(String year,Long userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("SELECT ORG.ORG_NAME,\n");
		sql.append("       TVMG.GROUP_NAME,\n");  
		sql.append("       TMP.ROW_NUMBER,\n");  
		sql.append("       TMP.SUM_AMT,\n");  
		sql.append("       TMP.JAN_AMT,\n");  
		sql.append("       TMP.FEB_AMT,\n");  
		sql.append("       TMP.MAR_AMT,\n");  
		sql.append("       TMP.APR_AMT,\n");  
		sql.append("       TMP.MAY_AMOUNT,\n");  
		sql.append("       TMP.JUN_AMT,\n");  
		sql.append("       TMP.JUL_AMT,\n");  
		sql.append("       TMP.AUG_AMT,\n");  
		sql.append("       TMP.SEP_AMT,\n");  
		sql.append("       TMP.OCT_AMT,\n");  
		sql.append("       TMP.NOV_AMT,\n");  
		sql.append("       TMP.DEC_AMT\n");  
		sql.append("  FROM TMP_VS_YEARLY_PLAN TMP, TM_ORG ORG, TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TMP.ORG_CODE = ORG.ORG_CODE\n");  
		sql.append("   AND TVMG.GROUP_CODE = TMP.GROUP_CODE\n");
		sql.append("   AND tmp.PLAN_YEAR=?\n");
		params.add(year);
		sql.append("   AND tmp.USER_ID=?\n");
		sql.append("   ORDER BY ORG.ORG_ID");
		params.add(userId);
		
		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	
	/*
	 * OEM导入时，查询临时表数据
	 */
	public List<Map<String, Object>> subSelectTmpYearPlan(String year,Long userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("SELECT DEALER.DEALER_SHORTNAME DEALER_NAME,\n");
		sql.append("       TVMG.GROUP_NAME,\n");  
		sql.append("       TMP.ROW_NUMBER,\n");  
		sql.append("       TMP.SUM_AMT,\n");  
		sql.append("       TMP.JAN_AMT,\n");  
		sql.append("       TMP.FEB_AMT,\n");  
		sql.append("       TMP.MAR_AMT,\n");  
		sql.append("       TMP.APR_AMT,\n");  
		sql.append("       TMP.MAY_AMOUNT,\n");  
		sql.append("       TMP.JUN_AMT,\n");  
		sql.append("       TMP.JUL_AMT,\n");  
		sql.append("       TMP.AUG_AMT,\n");  
		sql.append("       TMP.SEP_AMT,\n");  
		sql.append("       TMP.OCT_AMT,\n");  
		sql.append("       TMP.NOV_AMT,\n");  
		sql.append("       TMP.DEC_AMT\n");  
		sql.append("  FROM TMP_VS_YEARLY_PLAN TMP, TM_DEALER DEALER, TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append(" WHERE TMP.DEALER_CODE = DEALER.DEALER_CODE\n");  
		sql.append("   AND TVMG.GROUP_CODE = TMP.GROUP_CODE\n");
		sql.append("   AND tmp.PLAN_YEAR=?\n");
		params.add(year);
		sql.append("   AND tmp.USER_ID=?\n");
		sql.append("   ORDER BY DEALER.DEALER_ID");
		params.add(userId);
		
		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 插入业务表TT_YEARLY_PLAN
	 */
	public void insertTtYearlyPlan(TtVsYearlyPlanPO po){
		dao.insert(po);
	}
	
	public List<Map<String, Object>> selectTmpYearPlanUnConfirmed(String year,Long userId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select porg.ORG_ID,\n");
		sql.append("       porg.org_name,\n");  
		sql.append("       plan.GROUP_ID,\n");  
		sql.append("       sum(plan.PLAN_AMOUNT) amount\n");  
		sql.append("  from TM_ORG                 org,\n");  
		sql.append("       TT_VS_YEARLY_PLAN         plan,\n");  
		sql.append("       TM_DEALER_ORG_RELATION r,\n");  
		sql.append("       tm_org                 porg\n");  
		sql.append(" where org.ORG_ID = r.ORG_ID\n");  
		sql.append("   and porg.ORG_ID = org.PARENT_ORG_ID\n");  
		sql.append("   and plan.DEALER_ID = r.DEALER_ID\n");  
		sql.append("   and porg.ORG_TYPE = ?\n");  
		params.add(Constant.ORG_TYPE_OEM);
		sql.append("   and plan.plan_year = ?\n"); 
		params.add(year);
		sql.append("   and plan.create_by = ?\n"); 
		params.add(userId);
		sql.append(" group by porg.ORG_ID, porg.ORG_NAME, plan.GROUP_ID\n");  
		sql.append(" order by porg.ORG_ID");

		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 查询未确认年度目标，按区域、车系汇总
	 */
	public List<Map<String, Object>> selectTmpYearPlanUnConfirmed(List<Map<String, Object>> list,Map<String, Object> conMap){
		String companyId=conMap.get("companyId").toString();
		String areaId=conMap.get("areaId").toString();
		String planType=conMap.get("planType").toString();
		String year=conMap.get("year").toString();
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select\n");
		for(int i=0;i<list.size();i++){
			Map<String, Object> map=list.get(i);
			sql.append("       sum(decode(a.group_id, '"+map.get("GROUP_ID")+"', a.amount, '0')) a"+map.get("GROUP_ID")+",\n");  
		}
		sql.append("       c.org_id org_id,\n");
		sql.append("       c.org_name org_name\n");  

		sql.append("  from (select d.PLAN_ID,\n");
		sql.append("              d.MATERIAL_GROUPID group_id,\n");  
		sql.append("              sum(d.SALE_AMOUNT) amount\n");  
		sql.append("         from TT_VS_YEARLY_PLAN_DETAIL d\n");  
		sql.append("        group by d.PLAN_ID, d.MATERIAL_GROUPID) a,\n");  
		sql.append("      TT_VS_YEARLY_PLAN b,\n");  
		sql.append("      TM_ORG c\n");  
		sql.append("  where b.PLAN_ID = a.plan_id\n");  
		sql.append("    AND c.ORG_ID = B.ORG_ID\n");  
		sql.append("    AND b.COMPANY_ID="+companyId+"\n");
		sql.append("    AND b.AREA_ID="+areaId+"\n");
		sql.append("    AND b.PLAN_TYPE="+planType+"\n");
		sql.append("    and b.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("    and b.PLAN_YEAR = "+year+"\n"); 
		sql.append("  group by c.org_name, c.org_id");
		sql.append("  order by c.org_id");

		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName()+System.currentTimeMillis());
	}
	
	/*
	 * 区域查询未确认年度目标，按区域、车系汇总
	 */
	public List<Map<String, Object>> selectSubYearPlanUnConfirmed(List<Map<String,Object>> list,String year,String buss_area,String planTypes,Long userId,Long orgId,String companyId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select\n");
		for(int i=0;i<list.size();i++){
			Map<String,Object> map=list.get(i);
			sql.append("       sum(decode(a.group_id, '"+map.get("GROUP_ID")+"', a.amount, '0')) a"+map.get("GROUP_ID")+",\n");  
		} 
		sql.append("       c.DEALER_SHORTNAME DEALER_SHORTNAME,\n");  
		sql.append("       c.dealer_id dealer_id\n");  
		sql.append("  from (select d.PLAN_ID,\n");  
		sql.append("               d.MATERIAL_GROUPID group_id,\n");  
		sql.append("               sum(d.SALE_AMOUNT) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d\n");  
		sql.append("         group by d.PLAN_ID, d.MATERIAL_GROUPID) a,\n");  
		sql.append("       TT_VS_YEARLY_PLAN b,\n");  
		sql.append("       TM_DEALER c\n");  
		sql.append(" where b.PLAN_ID = a.plan_id\n");  
		sql.append("   AND c.DEALER_ID = b.DEALER_ID\n");
		sql.append("   AND b.COMPANY_ID = "+companyId+"\n");
		/*	先注释掉，待组织关系确定下来*/
		sql.append("   and b.DEALER_ID in\n");
		sql.append("       (select d.DEALER_ID\n");  
		sql.append("          from tm_dealer d\n");  
		sql.append("         start with d.DEALER_ID in\n");  
		sql.append("                    (select td.DEALER_ID\n");  
		sql.append("                       from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
		sql.append("                      where td.dealer_id = r.dealer_id\n");  
		sql.append("                        and td.OEM_COMPANY_ID = b.COMPANY_ID\n");  
		sql.append("                        and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("                        and r.ORG_ID = "+orgId+")\n");
		sql.append("connect by prior d.DEALER_ID = d.PARENT_DEALER_D\n");
		sql.append("        )");

		sql.append("    and b.STATUS = "+Constant.PLAN_MANAGE_UNCONFIRM+"\n");  
		sql.append("    and b.PLAN_YEAR = ? \n"); 
		params.add(year);
		sql.append("    and b.area_id = ?  \n"); 
		params.add(buss_area);
		sql.append("    and b.plan_type = ?  \n"); 
		params.add(planTypes);
		sql.append(" group by c.DEALER_SHORTNAME, c.dealer_id\n");  
		sql.append(" order by c.dealer_id");


		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName()+System.currentTimeMillis());
	}
	public List<Map<String, Object>> selectAllSeries(){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer("");

		sql.append("select g.GROUP_ID, g.GROUP_NAME\n");
		sql.append("  from TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append(" where g.GROUP_LEVEL = 2;");

		logger.info(sql.toString());
		
		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * 查询所有车系
	 */
	public List<TmVhclMaterialGroupPO> selectAllSeries(TmVhclMaterialGroupPO po){
		List<TmVhclMaterialGroupPO> list=new LinkedList<TmVhclMaterialGroupPO>();
		list=dao.select(po);
		return list;
	}
	/*
	 * 查询年度目标表最大的PLAN_VER
	 */
	public Integer selectMaxPlanVer(String year,Long userId,Integer status,String companyId,String orgType,String areaId,String planType){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select nvl(max(p.plan_ver),0) plan_ver\n");
		sql.append("  from TT_VS_YEARLY_PLAN p\n");  
		sql.append(" where p.STATUS = ?\n");  
		params.add(status);
		sql.append("   and p.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and p.COMPANY_ID = ?\n");  
		params.add(companyId);
		sql.append("   and p.ORG_TYPE = ?\n");  
		params.add(orgType);
		sql.append("   and p.PLAN_TYPE = ?\n");  
		params.add(planType);
		sql.append("   and p.AREA_ID = "+areaId+"\n");  

		Map<String, Object> map =dao.pageQueryMap(sql.toString(), params, "");
		
		Integer val=new Integer(map.get("PLAN_VER").toString());
	    return val;
		
	}
	/*
	 * 插入年计划明细
	 */
	public void insertTtYearlyPlanDetial(TtVsYearlyPlanDetailPO po){
		dao.insert(po);
	}
	
	/*
	 * 查询年度目标最大版本号
	 */
	public String selectMaxPlanVer(String year,String companyId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select nvl(max(plan_ver),0) plan_ver \n");
		sql.append("  from TT_VS_YEARLY_PLAN t\n");  
		sql.append(" where t.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and t.company_Id=?");
		params.add(companyId);
		sql.append("   and t.STATUS = "+Constant.PLAN_MANAGE_CONFIRM);

		Map<String, Object> map=dao.pageQueryMap(sql.toString(), params, "");
		
		return map.get("PLAN_VER").toString();
	}
	
	/*
	 * 查询年度目标最大版本号(查询用)
	 */
	public List<Map<String, Object>> selectOemMaxPlanVer(String year,String companyId,String chngType,String areaId,String planType){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		Integer orgType=new Integer(0);
		if("2".equals(chngType)){
			orgType=Constant.ORG_TYPE_OEM;
		}else{
			orgType=Constant.ORG_TYPE_DEALER;
		}

		sql.append("select distinct nvl(plan_ver,0) plan_ver \n");
		sql.append("  from TT_VS_YEARLY_PLAN t\n");  
		sql.append(" where t.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and t.company_Id=?");
		params.add(companyId);
		sql.append("   and t.org_type=?");
		params.add(orgType);
		sql.append("   and t.plan_type=?");
		params.add(planType);
		sql.append("   and t.area_id=?");
		params.add(areaId);
		sql.append("   and t.STATUS = "+Constant.PLAN_MANAGE_CONFIRM);
		sql.append("   order by plan_ver desc");
		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	/*
	 * dealer查询年度目标最大版本号(查询用)
	 */
	public List<Map<String, Object>> selectDealerMaxPlanVer(String year,String companyId,String dealerId,String areaId,String planType){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select distinct nvl(plan_ver,0) plan_ver \n");
		sql.append("  from TT_VS_YEARLY_PLAN t\n");  
		sql.append(" where t.PLAN_YEAR = ?\n");  
		params.add(year);
		sql.append("   and t.company_Id=?");
		params.add(companyId);
		sql.append("   and t.AREA_ID=?");
		params.add(areaId);
		sql.append("   and t.PLAN_TYPE=?");
		params.add(planType);
		sql.append("   and t.dealer_Id in ("+PlanUtil.createSqlStr(dealerId)+")");
		sql.append("   and t.STATUS = "+Constant.PLAN_MANAGE_CONFIRM);
		sql.append("   order by plan_ver desc");
		
		List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, getFunName());
		
		return list;
	}
	/*
	 * 判断是经销商还是组织
	 */
	public boolean isAreaOrg(Long orgId){
		List<Object> params = new LinkedList<Object>();
		boolean isAreaOrg=true;
		StringBuffer sql=new StringBuffer();

		sql.append("select distinct org_type\n");
		sql.append("  from tm_org org\n");  
		sql.append(" where org.PARENT_ORG_ID = ?");
        params.add(orgId);
		
        List<Map<String, Object>> list=dao.pageQuery(sql.toString(), params, "");
        if(null==list||list.size()==0){
        	isAreaOrg=false;
        }else{
        	Map<String, Object> map=list.get(0);
            Integer orgType=(Integer)map.get("ORG_TYPE");
            if(orgType.intValue()!=Constant.ORG_TYPE_DEALER){
            	isAreaOrg=false;
            }
        }
        return isAreaOrg;
	}
	/*
	 * 查询区域明细
	 */
	public PageResult<Map<String, Object>> oemSelectYearlyPlan(Map<String, Object> map,int pageSize,int curPage){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  
		sql.append("  from (select p.PLAN_ID, org.org_code, org.ORG_NAME\n");  
		sql.append("          from TT_VS_YEARLY_PLAN p, TM_ORG org\n");  
		sql.append("         where p.ORG_ID = org.ORG_ID\n");  
		sql.append("               and p.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");
		sql.append("               and p.COMPANY_ID = "+companyId+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("           and p.org_id =?\n");  
			params.add(logonOrgId);
		}
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("           and org.org_code in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			//params.add(orgCode);
		}
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
		
	}

	/*
	 * 查询经销商明细
	 */
	public PageResult<Map<String, Object>> oemSelectDealerYearlyPlan(Map<String, Object> map,int pageSize,int curPage){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       a.DEALER_CODE,\n"); 
		sql.append("       a.DEALER_SHORTNAME,\n"); 
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  

		sql.append("from (select  p.PLAN_ID,\n");
		sql.append("              org.org_code,\n");  
		sql.append("              org.ORG_NAME,\n");  
		sql.append("              td.DEALER_CODE,\n");  
		sql.append("              td.DEALER_SHORTNAME\n");  
		sql.append("         from TT_VS_YEARLY_PLAN         p,\n");  
		sql.append("              TM_ORG                 org,\n");  
		sql.append("              TM_DEALER              td,\n");  
		sql.append("              TM_DEALER_ORG_RELATION r\n");  
		sql.append("        where p.DEALER_ID = td.DEALER_ID\n");  
		sql.append("          and r.org_id = org.ORG_ID\n");  
		//sql.append("          and td.DEALER_ID = r.DEALER_ID\n"); 

		sql.append("          and (select t.dealer_id\n");
		sql.append("                  from TM_DEALER t\n");  
		sql.append("                 where t.DEALER_LEVEL = "+Constant.DEALER_LEVEL_01+"\n");  
		sql.append("                 start with t.DEALER_ID = p.dealer_id\n");  
		sql.append("                connect by prior t.PARENT_DEALER_D = t.DEALER_ID) =r.DEALER_ID\n");

		sql.append("          and p.COMPANY_ID = "+companyId+"\n");
		sql.append("          and p.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("           and r.org_id =?\n");  
			params.add(logonOrgId);
		}
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			//params.add(dealerCode);
		}
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
	}
	/*
	 * 查询所有车系
	 */
	public List<Map<String, Object>> selectSeries(String companyId){
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select g.GROUP_NAME,g.GROUP_ID\n");
		sql.append("  from TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append(" where g.GROUP_level = 2\n");  
		sql.append("    and g.COMPANY_ID="+companyId+"\n");  
		sql.append(" order by g.GROUP_ID");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	@Override
	protected PO wrapperPO(ResultSet rs, int idx) {
		// TODO Auto-generated method stub
		return null;
	}

	/*
	 * 经销商汇总
	 */
	public PageResult<Map<String, Object>> oemSelectDealerYearlyPlanTotal(Map<String, Object> map,List<Map<String, Object>> serlist,int pageSize,int curPage){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.DEALER_CODE,\n");  
		sql.append("       YEARLY.DEALER_SHORTNAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.MATERIAL_GROUPID,\n");  
			sql.append("                  "+sermap.get("GROUP_ID")+",\n");  
			sql.append("                  YEARLY.SALE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       sum(yearly.SALE_AMOUNT) SALE_AMOUNT,\n");
		sql.append("       YEARLY.DEALER_ID\n");  
		sql.append("  FROM (SELECT TVYP.DEALER_ID,\n");  
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");  
		sql.append("               TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("               nvl(SUM(TVYPD.SALE_AMOUNT),0) AS SALE_AMOUNT\n");  
		sql.append("          FROM TT_VS_YEARLY_PLAN        TVYP,\n");  
		sql.append("               TT_VS_YEARLY_PLAN_DETAIL TVYPD,\n");  
		sql.append("               TM_DEALER                TD\n");  
		sql.append("         WHERE TVYP.PLAN_ID = TVYPD.PLAN_ID\n");  
		sql.append("           AND TVYP.DEALER_ID = TD.DEALER_ID\n");
		sql.append("           AND TVYP.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVYP.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("      and TVYP.DEALER_ID in \n");
			sql.append("       (select d.DEALER_ID\n");  
			sql.append("          from tm_dealer d\n");  
			sql.append("         start with d.DEALER_ID in\n");  
			sql.append("                    (select td.DEALER_ID\n");  
			sql.append("                       from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
			sql.append("                      where td.dealer_id = r.dealer_id\n");  
			sql.append("                        and td.OEM_COMPANY_ID =TVYP.COMPANY_ID\n");  
			sql.append("                        and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
			sql.append("                        and r.ORG_ID = "+logonOrgId+")\n");
			sql.append("connect by prior d.DEALER_ID = d.PARENT_DEALER_D\n");
			sql.append("        )");
		}
		sql.append("           and TVYP.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and TVYP.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and TVYP.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and TVYP.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and TVYP.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and TVYP.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			//params.add(dealerCode);
		}
		
		sql.append("         GROUP BY TVYP.DEALER_ID,\n");  
		sql.append("                  TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("                  TD.DEALER_CODE,\n");  
		sql.append("                  TD.DEALER_SHORTNAME) YEARLY\n");  
		sql.append(" GROUP BY DEALER_ID, YEARLY.DEALER_CODE, YEARLY.DEALER_SHORTNAME\n");  
		sql.append(" order by dealer_id");

		return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
	}
	
	/*
	 * ORG汇总
	 */
	public PageResult<Map<String, Object>> oemSelectOrgYearlyPlanTotal(Map<String, Object> map,List<Map<String, Object>> serlist,int pageSize,int curPage){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.ORG_CODE,\n");  
		sql.append("       YEARLY.ORG_NAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.MATERIAL_GROUPID,\n");  
			sql.append("                  "+sermap.get("GROUP_ID")+",\n");  
			sql.append("                  YEARLY.SALE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       sum(yearly.SALE_AMOUNT) SALE_AMOUNT,\n");  
		sql.append("       YEARLY.ORG_ID\n");  
		sql.append("  FROM (SELECT TVYP.ORG_ID,\n");  
		sql.append("               ORG.ORG_CODE,\n");  
		sql.append("               ORG.ORG_NAME,\n");  
		sql.append("               TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("               nvl(SUM(TVYPD.SALE_AMOUNT), 0) AS SALE_AMOUNT\n");  
		sql.append("          FROM TT_VS_YEARLY_PLAN        TVYP,\n");  
		sql.append("               TT_VS_YEARLY_PLAN_DETAIL TVYPD,\n");  
		sql.append("               TM_ORG                   ORG\n");  
		sql.append("         WHERE TVYP.PLAN_ID = TVYPD.PLAN_ID\n");  
		sql.append("           AND TVYP.ORG_ID = ORG.ORG_ID\n");  
		sql.append("           AND TVYP.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVYP.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("           and TVYP.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and TVYP.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("       and TVYP.ORG_ID=?\n");
			params.add(logonOrgId);
		}
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and TVYP.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and TVYP.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and TVYP.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and TVYP.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("           and org.ORG_CODE in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			//params.add(dealerCode);
		}
		sql.append("         GROUP BY TVYP.ORG_ID,\n");  
		sql.append("                  TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("                  ORG.ORG_CODE,\n");  
		sql.append("                  ORG.ORG_NAME) YEARLY\n");  
		sql.append(" GROUP BY YEARLY.ORG_ID, YEARLY.ORG_CODE, YEARLY.ORG_NAME\n");  
		sql.append(" order by YEARLY.ORG_ID");


		return dao.pageQuery(sql.toString(), params, getFunName(), pageSize, curPage);
	}
	
	/*
	 * 经销商汇总
	 */
	public List<Map<String, Object>> oemSelectDealerYearlyPlanTotalDown(Map<String, Object> map,List<Map<String, Object>> serlist){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.DEALER_CODE,\n");  
		sql.append("       YEARLY.DEALER_SHORTNAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.MATERIAL_GROUPID,\n");  
			sql.append("                  "+sermap.get("GROUP_ID")+",\n");  
			sql.append("                  YEARLY.SALE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       sum(yearly.SALE_AMOUNT) SALE_AMOUNT,\n");
		sql.append("       YEARLY.DEALER_ID\n");  
		sql.append("  FROM (SELECT TVYP.DEALER_ID,\n");  
		sql.append("               TD.DEALER_CODE,\n");  
		sql.append("               TD.DEALER_SHORTNAME,\n");  
		sql.append("               TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("               nvl(SUM(TVYPD.SALE_AMOUNT),0) AS SALE_AMOUNT\n");  
		sql.append("          FROM TT_VS_YEARLY_PLAN        TVYP,\n");  
		sql.append("               TT_VS_YEARLY_PLAN_DETAIL TVYPD,\n");  
		sql.append("               TM_DEALER                TD\n");  
		sql.append("         WHERE TVYP.PLAN_ID = TVYPD.PLAN_ID\n");  
		sql.append("           AND TVYP.DEALER_ID = TD.DEALER_ID\n");
		sql.append("           AND TVYP.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVYP.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("      and TVYP.DEALER_ID in \n");
			sql.append("       (select d.DEALER_ID\n");  
			sql.append("          from tm_dealer d\n");  
			sql.append("         start with d.DEALER_ID in\n");  
			sql.append("                    (select td.DEALER_ID\n");  
			sql.append("                       from TM_DEALER td, TM_DEALER_ORG_RELATION r\n");  
			sql.append("                      where td.dealer_id = r.dealer_id\n");  
			sql.append("                        and td.OEM_COMPANY_ID =TVYP.COMPANY_ID\n");  
			sql.append("                        and td.STATUS = "+Constant.STATUS_ENABLE+"\n");  
			sql.append("                        and r.ORG_ID = "+logonOrgId+")\n");
			sql.append("connect by prior d.DEALER_ID = d.PARENT_DEALER_D\n");
			sql.append("        )");
		}
		sql.append("           and TVYP.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and TVYP.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and TVYP.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and TVYP.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and TVYP.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and TVYP.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			//params.add(dealerCode);
		}
		
		sql.append("         GROUP BY TVYP.DEALER_ID,\n");  
		sql.append("                  TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("                  TD.DEALER_CODE,\n");  
		sql.append("                  TD.DEALER_SHORTNAME) YEARLY\n");  
		sql.append(" GROUP BY DEALER_ID, YEARLY.DEALER_CODE, YEARLY.DEALER_SHORTNAME\n");  
		sql.append(" order by dealer_id");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	
	/*
	 * ORG汇总下载
	 */
	public List<Map<String, Object>> oemSelectOrgYearlyPlanTotalDown(Map<String, Object> map,List<Map<String, Object>> serlist){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("\n");
		sql.append("SELECT YEARLY.ORG_CODE,\n");  
		sql.append("       YEARLY.ORG_NAME,\n");  
		for(int i=0;i<serlist.size();i++){
			Map<String, Object> sermap=serlist.get(i);
			sql.append("       nvl(MAX(DECODE(YEARLY.MATERIAL_GROUPID,\n");  
			sql.append("                  "+sermap.get("GROUP_ID")+",\n");  
			sql.append("                  YEARLY.SALE_AMOUNT)),0) S"+i+",\n");  
		}
		sql.append("       sum(yearly.SALE_AMOUNT) SALE_AMOUNT,\n");  
		sql.append("       YEARLY.ORG_ID\n");  
		sql.append("  FROM (SELECT TVYP.ORG_ID,\n");  
		sql.append("               ORG.ORG_CODE,\n");  
		sql.append("               ORG.ORG_NAME,\n");  
		sql.append("               TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("               nvl(SUM(TVYPD.SALE_AMOUNT), 0) AS SALE_AMOUNT\n");  
		sql.append("          FROM TT_VS_YEARLY_PLAN        TVYP,\n");  
		sql.append("               TT_VS_YEARLY_PLAN_DETAIL TVYPD,\n");  
		sql.append("               TM_ORG                   ORG\n");  
		sql.append("         WHERE TVYP.PLAN_ID = TVYPD.PLAN_ID\n");  
		sql.append("           AND TVYP.ORG_ID = ORG.ORG_ID\n");  
		sql.append("           AND TVYP.COMPANY_ID="+companyId+"\n");
		sql.append("           AND TVYP.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("           and TVYP.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and TVYP.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("       and TVYP.ORG_ID=?\n");
			params.add(logonOrgId);
		}
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and TVYP.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and TVYP.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and TVYP.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and TVYP.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("           and org.ORG_CODE in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			//params.add(dealerCode);
		}
		sql.append("         GROUP BY TVYP.ORG_ID,\n");  
		sql.append("                  TVYPD.MATERIAL_GROUPID,\n");  
		sql.append("                  ORG.ORG_CODE,\n");  
		sql.append("                  ORG.ORG_NAME) YEARLY\n");  
		sql.append(" GROUP BY YEARLY.ORG_ID, YEARLY.ORG_CODE, YEARLY.ORG_NAME\n");  
		sql.append(" order by YEARLY.ORG_ID");


		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	
	/*
	 * 查询区域明细
	 */
	public List<Map<String, Object>> oemSelectOrgDetailYearlyPlanDown(Map<String, Object> map){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String orgCode=(String)map.get("orgCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  
		sql.append("  from (select p.PLAN_ID, org.org_code, org.ORG_NAME\n");  
		sql.append("          from TT_VS_YEARLY_PLAN p, TM_ORG org\n");  
		sql.append("         where p.ORG_ID = org.ORG_ID\n"); 
		sql.append("           	   AND p.COMPANY_ID="+companyId+"\n");
		sql.append("               and p.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("           and p.org_id =?\n");  
			params.add(logonOrgId);
		}
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=orgCode&&!"".equals(orgCode)){
			sql.append("           and org.org_code in ("+PlanUtil.createSqlStr(orgCode)+")\n");  
			//params.add(orgCode);
		}
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName());
		
	}

	/*
	 * 查询经销商明细
	 */
	public List<Map<String, Object>> oemSelectDealerDetailYearlyPlanDown(Map<String, Object> map){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerCode=(String)map.get("dealerCode");
		String logonOrgType=(String)map.get("logonOrgType");
		String logonOrgId=(String)map.get("logonOrgId");
		String companyId=map.get("companyId").toString();

		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       a.DEALER_CODE,\n"); 
		sql.append("       a.DEALER_SHORTNAME,\n"); 
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  

		sql.append("from (select  p.PLAN_ID,\n");
		sql.append("              org.org_code,\n");  
		sql.append("              org.ORG_NAME,\n");  
		sql.append("              td.DEALER_CODE,\n");  
		sql.append("              td.DEALER_SHORTNAME\n");  
		sql.append("         from TT_VS_YEARLY_PLAN         p,\n");  
		sql.append("              TM_ORG                 org,\n");  
		sql.append("              TM_DEALER              td,\n");  
		sql.append("              TM_DEALER_ORG_RELATION r\n");  
		sql.append("        where p.DEALER_ID = td.DEALER_ID\n");  
		sql.append("          and r.org_id = org.ORG_ID\n");  
		//sql.append("          and td.DEALER_ID = r.DEALER_ID\n"); 
		sql.append("          and (select t.dealer_id\n");
		sql.append("                  from TM_DEALER t\n");  
		sql.append("                 where t.DEALER_LEVEL = "+Constant.DEALER_LEVEL_01+"\n");  
		sql.append("                 start with t.DEALER_ID = p.dealer_id\n");  
		sql.append("                connect by prior t.PARENT_DEALER_D = t.DEALER_ID) =r.DEALER_ID\n");
		sql.append("          AND p.COMPANY_ID="+companyId+"\n");
		sql.append("          and p.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		if(null!=logonOrgId&&null!=logonOrgType&&!"".equals(logonOrgId)&&"LARGEREGION".equals(logonOrgType)){
			sql.append("           and r.org_id =?\n");  
			params.add(logonOrgId);
		}
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		if(null!=dealerCode&&!"".equals(dealerCode)){
			sql.append("           and td.dealer_code in ("+PlanUtil.createSqlStr(dealerCode)+")\n");  
			//params.add(dealerCode);
		}
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	/*
	 * DEALER查询经销商明细
	 */
	public PageResult<Map<String, Object>> dealerSelectDetailYearlyPlan(Map<String, Object> map,int pageSize,int curPage){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerId=(String)map.get("dealerId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();
		

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       a.DEALER_CODE,\n"); 
		sql.append("       a.DEALER_SHORTNAME,\n"); 
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  

		sql.append("from (select  p.PLAN_ID,\n");
		sql.append("              org.org_code,\n");  
		sql.append("              org.ORG_NAME,\n");  
		sql.append("              td.DEALER_CODE,\n");  
		sql.append("              td.DEALER_SHORTNAME\n");  
		sql.append("         from TT_VS_YEARLY_PLAN         p,\n");  
		sql.append("              TM_ORG                 org,\n");  
		sql.append("              TM_DEALER              td,\n");  
		sql.append("              TM_DEALER_ORG_RELATION r\n");  
		sql.append("        where p.DEALER_ID = td.DEALER_ID\n");  
		sql.append("          and r.org_id = org.ORG_ID\n");  
		sql.append("          and td.DEALER_ID = r.DEALER_ID\n"); 
		sql.append("          AND p.COMPANY_ID="+companyId+"\n");
		sql.append("          and p.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		//sql.append("          and td.DEALER_ID in ("+PlanUtil.createSqlStr(dealerId)+")\n"); 
		sql.append("          and p.DEALER_ID in\n");
		sql.append("            (select d.DEALER_ID\n");  
		sql.append("               from tm_dealer d\n");  
		sql.append("             start with d.DEALER_ID in\n");  
		sql.append("                   ("+PlanUtil.createSqlStr(dealerId)+")\n");
		sql.append("   connect by prior d.DEALER_ID = d.PARENT_DEALER_D)\n");
		
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName(),pageSize,curPage);
	}
	/*
	 * DEALER查询经销商明细下载
	 */
	public List<Map<String, Object>> dealerSelectDetailYearlyPlanDown(Map<String, Object> map){
		String plan_year=(String)map.get("plan_year");
		String plan_ver=(String)map.get("plan_ver");
		String plan_desc=(String)map.get("plan_desc");
		String buss_area=(String)map.get("buss_area");
		String planType=(String)map.get("planType");
		String dealerId=(String)map.get("dealerId");
		String companyId=map.get("companyId").toString();
		
		List<Object> params = new LinkedList<Object>();
		StringBuffer sql=new StringBuffer();

		sql.append("select a.ORG_CODE,\n");
		sql.append("       a.ORG_NAME,\n");  
		sql.append("       a.DEALER_CODE,\n"); 
		sql.append("       a.DEALER_SHORTNAME,\n"); 
		sql.append("       b.group_code,\n");  
		sql.append("       b.group_name,\n");  
		sql.append("       c.amount,\n");  
		sql.append("       b.m1,\n");  
		sql.append("       b.m2,\n");  
		sql.append("       b.m3,\n");  
		sql.append("       b.m4,\n");  
		sql.append("       b.m5,\n");  
		sql.append("       b.m6,\n");  
		sql.append("       b.m7,\n");  
		sql.append("       b.m8,\n");  
		sql.append("       b.m9,\n");  
		sql.append("       b.m10,\n");  
		sql.append("       b.m11,\n");  
		sql.append("       b.m12\n");  

		sql.append("from (select  p.PLAN_ID,\n");
		sql.append("              org.org_code,\n");  
		sql.append("              org.ORG_NAME,\n");  
		sql.append("              td.DEALER_CODE,\n");  
		sql.append("              td.DEALER_SHORTNAME\n");  
		sql.append("         from TT_VS_YEARLY_PLAN         p,\n");  
		sql.append("              TM_ORG                 org,\n");  
		sql.append("              TM_DEALER              td,\n");  
		sql.append("              TM_DEALER_ORG_RELATION r\n");  
		sql.append("        where p.DEALER_ID = td.DEALER_ID\n");  
		sql.append("          and r.org_id = org.ORG_ID\n");  
		sql.append("          and td.DEALER_ID = r.DEALER_ID\n"); 
		sql.append("          AND p.COMPANY_ID="+companyId+"\n");
		sql.append("          and p.ORG_TYPE = "+Constant.ORG_TYPE_DEALER+"\n");
		//sql.append("          and td.DEALER_ID in ("+PlanUtil.createSqlStr(dealerId)+")\n");
		sql.append("          and p.DEALER_ID in\n");
		sql.append("            (select d.DEALER_ID\n");  
		sql.append("               from tm_dealer d\n");  
		sql.append("             start with d.DEALER_ID in\n");  
		sql.append("                   ("+PlanUtil.createSqlStr(dealerId)+")\n");
		sql.append("   connect by prior d.DEALER_ID = d.PARENT_DEALER_D)\n");
		sql.append("           and p.plan_year = ?\n");  
		params.add(plan_year);
		sql.append("           and p.STATUS = ?\n");  
		params.add(Constant.PLAN_MANAGE_CONFIRM);
		if(null!=plan_ver&&!"".equals(plan_ver)){
			sql.append("           and p.PLAN_VER = ?\n");  
			params.add(plan_ver);
		}
		if(null!=plan_desc&&!"".equals(plan_desc)){
			sql.append("           and p.PLAN_DESC like ?\n");  
			params.add(plan_desc);
		}
		if(null!=buss_area&&!"".equals(buss_area)){
			sql.append("           and p.area_id = ?\n");  
			params.add(buss_area);
		}
		if(null!=planType&&!"".equals(planType)){
			sql.append("           and p.plan_type = ?\n");  
			params.add(planType);
		}
		
		sql.append(") A, \n");
		sql.append("       (select d.PLAN_ID,\n");  
		sql.append("               g.GROUP_CODE,\n");  
		sql.append("               g.GROUP_NAME,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 1, d.SALE_AMOUNT, 0)) m1,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 2, d.SALE_AMOUNT, 0)) m2,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 3, d.SALE_AMOUNT, 0)) m3,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 4, d.SALE_AMOUNT, 0)) m4,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 5, d.SALE_AMOUNT, 0)) m5,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 6, d.SALE_AMOUNT, 0)) m6,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 7, d.SALE_AMOUNT, 0)) m7,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 8, d.SALE_AMOUNT, 0)) m8,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 9, d.SALE_AMOUNT, 0)) m9,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 10, d.SALE_AMOUNT, 0)) m10,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 11, d.SALE_AMOUNT, 0)) m11,\n");  
		sql.append("               sum(decode(d.PLAN_MONTH, 12, d.SALE_AMOUNT, 0)) m12\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d, TM_VHCL_MATERIAL_GROUP g\n");  
		sql.append("         where g.GROUP_ID = d.MATERIAL_GROUPID\n");  
		sql.append("         group by d.PLAN_ID, g.GROUP_CODE, g.GROUP_NAME\n");  
		sql.append("         order by d.PLAN_ID) B,\n");  
		sql.append("       (select d1.plan_id plan_id, sum(d1.sale_amount) amount\n");  
		sql.append("          from TT_VS_YEARLY_PLAN_DETAIL d1\n");  
		sql.append("         group by d1.PLAN_ID) C\n");  
		sql.append(" where a.PLAN_ID = b.plan_id\n");  
		sql.append("   and a.plan_id = c.plan_id\n");
		sql.append("   order by a.ORG_CODE");

		return dao.pageQuery(sql.toString(), params, getFunName());
	}
	
	
	/**
	 * 年度目标导入模板下载
	 * @param companyId
	 * @return
	 * @author zouchao
	 * @since  2010-08-20
	 */
	public List<Map<String, Object>> getTempDownload(String companyId, String areaId){
		
		StringBuffer sql= new StringBuffer();
		
		sql.append("SELECT A.ORG_CODE, A.ORG_NAME, B.GROUP_CODE, B.GROUP_NAME\n");
		sql.append("  FROM (SELECT TOR.ORG_ID, TOR.ORG_CODE, TOR.ORG_NAME\n");  
		sql.append("          FROM TM_ORG TOR,TM_ORG_BUSINESS_AREA C\n");  
		sql.append("         WHERE TOR.ORG_ID = C.ORG_ID \n");  
		sql.append("           AND TOR.ORG_TYPE = "+Constant.ORG_TYPE_OEM+"\n");  
		sql.append("           AND TOR.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("           AND TOR.DUTY_TYPE = "+Constant.DUTY_TYPE_LARGEREGION+"\n");  
		sql.append("           AND C.AREA_ID = ").append(areaId).append("\n");
		sql.append("           AND TOR.COMPANY_ID = "+companyId+") A,\n");  
		sql.append("       (SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("         WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("           AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAG\n");  
		sql.append("                      WHERE TAG.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID ---根据业范围往上找，找到车系级别\n");  
		sql.append("        UNION\n");  
		sql.append("        SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("         WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("           AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAG\n");  
		sql.append("                      WHERE TAG.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID) B\n");
		sql.append("ORDER BY A.ORG_CODE, B.GROUP_CODE");

		return dao.pageQuery(sql.toString(), null, getFunName());
	}
	
	/**
	 * 年度目标导入模板下载(区域)
	 * 月度任务导入模板下载
	 * @param orgId
	 * @param companyId
	 * @return
	 * @author zouchao
	 * @since  2010-08-20
	 */
	public List<Map<String, Object>> getTempDownloadByOrg(String orgId,String companyId, String areaId){
		StringBuffer sql= new StringBuffer("\n");

		sql.append("SELECT distinct A.ORG_NAME, A.DEALER_CODE, A.DEALER_NAME, B.GROUP_CODE, B.GROUP_NAME\n");
		sql.append("  FROM (SELECT D.DEALER_ID, D.DEALER_CODE, D.DEALER_NAME, O.ORG_NAME\n");  
		sql.append("          FROM TM_ORG                  O,\n");  
		sql.append("               TM_DEALER               D,\n");  
		sql.append("               TM_DEALER_BUSINESS_AREA C,\n");  
		sql.append("               TM_DEALER_ORG_RELATION  A\n");  
		sql.append("         WHERE D.DEALER_ID = C.DEALER_ID\n");  
		sql.append("           AND D.DEALER_ID = A.DEALER_ID\n");  
		sql.append("           AND O.ORG_ID = A.ORG_ID\n");  
		sql.append("           AND D.DEALER_TYPE IN (" + Constant.DEALER_TYPE_DVS + ", " + Constant.DEALER_TYPE_QYZDL + ")\n");  
		sql.append("           AND D.STATUS = " + Constant.STATUS_ENABLE + "\n");  
		if(null != orgId && !"".equals(orgId)){
			sql.append("	   AND A.ORG_ID = ").append(orgId).append("\n");
		}
		if(null != areaId && !"".equals(areaId)) {
			sql.append("           AND C.AREA_ID = " + areaId + "\n");  
		}
		sql.append("           AND D.DEALER_CLASS = " + Constant.DEALER_CLASS_TYPE_01  + "\n");  
		if (null !=  companyId && !"".equals(companyId)) {
			sql.append("           AND D.OEM_COMPANY_ID = " + companyId + "\n");  
		}
		sql.append("        UNION ALL\n");  
		sql.append("        SELECT TMD2.DEALER_ID,\n");  
		sql.append("               TMD2.DEALER_CODE,\n");  
		sql.append("               TMD2.DEALER_NAME,\n");  
		sql.append("               TMO.ORG_NAME\n");  
		sql.append("          FROM TM_ORG                  TMO,\n");  
		sql.append("               TM_DEALER               TMD1,\n");  
		sql.append("               TM_DEALER               TMD2,\n");  
		sql.append("               TM_DEALER_BUSINESS_AREA TMDBA,\n");  
		sql.append("               TM_DEALER_ORG_RELATION  TMDOR\n");  
		sql.append("         WHERE TMD1.DEALER_ID = TMDOR.DEALER_ID\n");  
		sql.append("           AND TMO.ORG_ID = TMDOR.ORG_ID\n");  
		sql.append("           AND TMD1.DEALER_ID = TMD2.PARENT_DEALER_D\n");  
		sql.append("           AND TMD2.DEALER_ID = TMDBA.DEALER_ID\n");  
		sql.append("           AND TMD1.DEALER_TYPE IN (" + Constant.DEALER_TYPE_JSZX + ", " + Constant.DEALER_TYPE_QYZDL + ")\n");  
		sql.append("           AND TMD1.STATUS = " + Constant.STATUS_ENABLE  + "\n");  
		sql.append("           AND TMD2.STATUS = " + Constant.STATUS_ENABLE  + "\n");  
		sql.append("           AND TMD2.DEALER_CLASS = " + Constant.DEALER_CLASS_TYPE_01  + "\n");  
		if(null != orgId && !"".equals(orgId)){
			sql.append("	   AND TMO.ORG_ID = ").append(orgId).append("\n");
		}
		if(null != areaId && !"".equals(areaId)) {
			sql.append("           AND TMDBA.AREA_ID = " + areaId + "\n");  
		}
		if (null !=  companyId && !"".equals(companyId)) {
			sql.append("           AND TMD1.OEM_COMPANY_ID = " + companyId + "\n"); 
		}
		sql.append("		) A,\n") ;
		sql.append("       (SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("         WHERE TVMG.GROUP_LEVEL = 2\n");  
		if (null !=  companyId && !"".equals(companyId)) {
			sql.append("           AND TVMG.COMPANY_ID = " + companyId + "\n");
		}
		sql.append("           AND TVMG.STATUS =  " + Constant.STATUS_ENABLE  + "\n");  
		sql.append("         START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAG\n");  
		if(null != areaId && !"".equals(areaId)) {
			sql.append("                      WHERE TAG.AREA_ID = " + areaId + ")\n"); 
		}
		sql.append("        CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID ---根据业范围往上找，找到车系级别\n");  
		sql.append("        ) B\n");  
		sql.append(" ORDER BY A.ORG_NAME, A.DEALER_CODE, B.GROUP_CODE\n");

		
		/*sql.append("SELECT A.ORG_NAME,A.DEALER_CODE, A.DEALER_NAME, B.GROUP_CODE, B.GROUP_NAME\n");
		sql.append("  FROM (SELECT  D.DEALER_ID, D.DEALER_CODE, D.DEALER_NAME,O.ORG_NAME\n");  
		sql.append("          FROM TM_ORG O,TM_DEALER D,TM_DEALER_BUSINESS_AREA C, TM_DEALER_ORG_RELATION A\n");  
		sql.append("         WHERE D.DEALER_ID = C.DEALER_ID \n");  
		sql.append("		   AND D.DEALER_ID = A.DEALER_ID\n");
		sql.append("		   AND O.ORG_ID = A.ORG_ID\n");
		// sql.append("		   AND A.BUSINESS_TYPE = ").append(Constant.ORG_TYPE_OEM).append("\n");
		sql.append("           AND D.DEALER_TYPE IN ("+Constant.DEALER_TYPE_DVS+", "+Constant.DEALER_TYPE_QYZDL+")\n");  
		sql.append("           AND D.STATUS = "+Constant.STATUS_ENABLE+"\n");
		if(!"".equals(orgId)){
			sql.append("	   AND A.ORG_ID = ").append(orgId).append("\n");
		}
		sql.append("           AND C.AREA_ID = ").append(areaId).append("\n");
		sql.append("           AND D.DEALER_CLASS = " + Constant.DEALER_CLASS_TYPE_01  + "\n");
		sql.append("           AND D.OEM_COMPANY_ID = "+companyId+") A,\n");  
		sql.append("       (SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("         WHERE TVMG.GROUP_LEVEL = 2\n");  
		sql.append("           AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAG\n");  
		sql.append("                      WHERE TAG.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR TVMG.PARENT_GROUP_ID = TVMG.GROUP_ID ---根据业范围往上找，找到车系级别\n");  
		sql.append("        UNION\n");  
		sql.append("        SELECT TVMG.GROUP_ID, TVMG.GROUP_CODE, TVMG.GROUP_NAME\n");  
		sql.append("          FROM TM_VHCL_MATERIAL_GROUP TVMG\n");  
		sql.append("         WHERE TVMG.GROUP_LEVEL = 2\n"); 
		sql.append("           AND TVMG.COMPANY_ID = "+companyId+"\n");  
		sql.append("           AND TVMG.STATUS = "+Constant.STATUS_ENABLE+"\n");  
		sql.append("         START WITH TVMG.GROUP_ID IN\n");  
		sql.append("                    (SELECT TAG.MATERIAL_GROUP_ID\n");  
		sql.append("                       FROM TM_AREA_GROUP TAG\n");  
		sql.append("                      WHERE TAG.AREA_ID = "+areaId+")\n");  
		sql.append("        CONNECT BY PRIOR TVMG.GROUP_ID = TVMG.PARENT_GROUP_ID) B\n");
		sql.append("ORDER BY A.ORG_NAME, A.DEALER_CODE, B.GROUP_CODE");*/
		
		
		return dao.pageQuery(sql.toString(), null, getFunName());

	}
}
